package cn.edu.nju.software.sqlitemanage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.edu.nju.software.beans.CidSidBean;

public class ManageXueshengXueke {
	// for student choose course
	// while the
	public static boolean chooseCourse(ArrayList<CidSidBean> csBeans)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		boolean updateOk = false;
		
		try {
			String command = "INSERT INTO xueshengxueke VALUES (?,?)";
			PreparedStatement prepareState = conn.prepareStatement(command);
			
			for (CidSidBean csBean : csBeans) {
				String sid = csBean.getSId();
				String cid = csBean.getCId();

				prepareState.setString(2, sid);
				prepareState.setString(1, cid);

				prepareState.executeUpdate();
			}
			updateOk = true;
		}catch(Exception exc){
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// for student give up choose
	public static boolean giveUpCourse(ArrayList<CidSidBean> csBeans)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		boolean updateOk = false;
		
		try {
			String command = "DELETE FROM xueshengxueke WHERE sid = ? AND cid = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			
			for(CidSidBean csb : csBeans){
				prepareState.setString(1, csb.getSId());
				prepareState.setString(2, csb.getCId());
				
				prepareState.executeUpdate();
			}
			
			updateOk = true;
		}catch(Exception exc){
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// get chosen courses by given student id
	public static ArrayList<String> getChosenCourses(String sid)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		ArrayList<String> cids = new ArrayList<String>();

		try {
			String command = "SELECT * FROM xueshengxueke WHERE sid = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			prepareState.setString(1, sid);
			
			ResultSet rs = prepareState.executeQuery();
			while(rs.next()){
				String cid = rs.getString(1);
				cids.add(cid);
			}
		}catch(Exception exc){
			
		} finally {
			conn.close();
		}

		return cids;
	}

	// get student id list by given to be deleted course id
	public static ArrayList<String> getDeleteCoReSt(String cid)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		ArrayList<String> sids = new ArrayList<String>();

		try {
			String command = "SELECT * FROM xueshengxueke WHERE cid = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			prepareState.setString(1, cid);
			
			ResultSet rs = prepareState.executeQuery();
			while(rs.next()){
				String sid = rs.getString(2);
				sids.add(sid);
			}
		}catch(Exception exc){
			
		} finally {
			conn.close();
		}

		return sids;
	}

	// get course id list by given to be deleted student id
	public static ArrayList<String> getDeleteStReCo(String sid)
			throws SQLException, ClassNotFoundException {

		ArrayList<String> cids = new ArrayList<String>();
		
		cids = ManageXueshengXueke.getChosenCourses(sid);

		return cids;
	}
}
